IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverableTypeStatus') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwInfoEdCSCRDeliverableTypeStatus
    IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverableTypeStatus') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwInfoEdCSCRDeliverableTypeStatus >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwInfoEdCSCRDeliverableTypeStatus >>>'
END
GO
-- =======================================================================
-- Object Name: (VIEW) dbo.vwInfoEdCSCRDeliverableTypeStatus
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 9/14/2007 
--
-- Description: View to Deliverable TYPE Statuses, for CSCR Reports.
--        This View "Flattens" out each DeliverableType Status and Date
-- 
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- RULE FOR DISPLAYING STATUS AND STATUS DATE FOR EACH TYPE OF DELIVERABLE IN 
--   THE MAIN BODY OF THE REPORT.
-- Status 
-- Show whatever Status is on the latest deliverable of each type the database. 
-- For example, with 4 Amendments, show the status of the 4th...
-- 
-- For Converted Data: If the Status is "CSCR - Completed" or "Completed", 
--   show the Completed Date under the Status.
-- For New Data: If the Status is "CSCR - Completed" or "Completed" use the 
--    Status Date on the Instance where the Deliverable FIRST became Completed...
--   (earliest date)
--  
-- Indicator of Converted vs. New Data: Submitted Date is NULL for New Data, 
--   and is NOT NULL for Converted Data...so this needs to be check along with 
--   above.
--  
-- For Both Converted and New: If the Status is "Pending" show the Minimum, or 
--   earliest Status Date for the Deliverable (in effect, the date the 
--   Deliverable was Created). 
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- REWRITE LOGIC!
-- Status 
-- Show whatever Status is on the latest deliverable of each type the database. 
-- For example, with 4 Amendments, show the status of the 4th...
-- 
-- Get the Latest Status Date by Proposal and REPORTCAT
-- IF a CompletedDate is Available, and it's greater than the StatusDate, use
--   the CompletedDate.
--  
-- Use this date to get the Status.
--
-- IF the Latest Status is Pending, then use the earliest date that it went
-- to Pending. (since adding comments also adds a new StatusDate).
--
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

-- Used BY Procedure/Report: dbo.RptCSCRPendingClinicalTrial
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 9/14/07     KC        Created
-- 10/2/07     KC        Fix for Pending return when it's Completed, yet
--                       without a CompletedDate
-- 10/3/07     KC        Put fix in to drop Time Component for StatusDate
-- 10/23/07    KC        Fixed Issue with Converted Deliverables being 
--                       completed after the conversion, but not showing up
--                       Properly!!!
-- 11/29/07    KC        Changed MAX(Status) to MIN(Status) because it was
--                       returning Pending instead of CSCR-Completed for
--                       the same day...
--                       Also, fixed Converted Records set to Completed (again)
-- 12/4/07     KC        Added, CSCR-Closed to Completed Status Check
--
-- 1/8/07      KC        Total ReWrite!!!! 
-- 1/30/08     KC        Redeploy, was named, vwInfoEdCSCRDeliverableTypeStatusNew
-- 2/11/08     KC        Changed to use ProcessedDate instead of StatusDate
-- 03/26/08    KC        Added Subcontract
-- 05/13/08    KC        Added WorkingBudgetAmendment
-- 
-- =======================================================================
CREATE VIEW dbo.vwInfoEdCSCRDeliverableTypeStatus
AS

SELECT    prop_no,
          MIN(STATUS.CSCRPIQStatus) AS CSCRPIQStatus,
          MAX(STATUS.CSCRPIQStatusDate) AS CSCRPIQStatusDate,
          MIN(STATUS.CSCRContractStatus) AS CSCRContractStatus,
          MAX(STATUS.CSCRContractStatusDate) AS CSCRContractStatusDate,
          MIN(STATUS.CSCRWorkingBudgetStatus) AS CSCRWorkingBudgetStatus,
          MAX(STATUS.CSCRWorkingBudgetStatusDate) AS CSCRWorkingBudgetStatusDate,
          MIN(STATUS.CSCRAmendmentStatus) AS CSCRAmendmentStatus,
          MAX(STATUS.CSCRAmendmentStatusDate) AS CSCRAmendmentStatusDate,
          MIN(STATUS.CSCRSubContractStatus) AS CSCRSubContractStatus,
          MAX(STATUS.CSCRSubContractStatusDate) AS CSCRSubContractStatusDate,
          MIN(STATUS.CSCRWorkingBudgetAmendmentStatus) AS CSCRWorkingBudgetAmendmentStatus,
          MAX(STATUS.CSCRWorkingBudgetAmendmentStatusDate) AS CSCRWorkingBudgetAmendmentStatusDate
FROM
     (-- Spread out Records to their own Columns
     SELECT    L.prop_no,
               CASE L.REPORTCAT 
                    WHEN 'CSCR - PIQ' THEN CONVERT(VARCHAR(20),L.REPORTSTAT)
                    ELSE NULL
               END AS 'CSCRPIQStatus',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - PIQ' THEN L.STATUSDATE
                    ELSE NULL
               END AS 'CSCRPIQStatusDate',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Contract' THEN CONVERT(VARCHAR(20),L.REPORTSTAT)
                    ELSE NULL
               END AS 'CSCRContractStatus',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Contract' THEN L.STATUSDATE
                    ELSE NULL
               END AS 'CSCRContractStatusDate',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Working Budget' THEN CONVERT(VARCHAR(20),L.REPORTSTAT)
                    ELSE NULL
               END AS 'CSCRWorkingBudgetStatus',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Working Budget' THEN L.STATUSDATE
                    ELSE NULL
               END AS 'CSCRWorkingBudgetStatusDate',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Amendment' THEN CONVERT(VARCHAR(20),L.REPORTSTAT)
                    ELSE NULL
               END AS 'CSCRAmendmentStatus',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Amendment' THEN L.STATUSDATE
                    ELSE NULL
                    END AS 'CSCRAmendmentStatusDate',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Subcontract' THEN CONVERT(VARCHAR(20),L.REPORTSTAT)
                    ELSE NULL
               END AS 'CSCRSubContractStatus',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Subcontract' THEN L.STATUSDATE
                    ELSE NULL
                    END AS 'CSCRSubContractStatusDate',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Working Budget Amendment' THEN CONVERT(VARCHAR(20),L.REPORTSTAT)
                    ELSE NULL
               END AS 'CSCRWorkingBudgetAmendmentStatus',
               CASE L.REPORTCAT 
                    WHEN 'CSCR - Working Budget Amendment' THEN L.STATUSDATE
                    ELSE NULL
                    END AS 'CSCRWorkingBudgetAmendmentStatusDate'
     FROM (     
          -- Get Latest StatusDates and their Statuses, but join with Early Pending Dates for these statuses
          -- so we get the initial Pending date where it is still Pending
          SELECT    LATEST.prop_no,
                    LATEST.REPORTCAT,
                    LATEST.REPORTSTAT,
                    -- If the latest status is Pending, we want the first record where it went pending this time
                    CASE WHEN UPPER(LATEST.REPORTSTAT) = 'PENDING' AND PENDING.STATUSDATE IS NOT NULL
                         THEN PENDING.STATUSDATE
                         ELSE LATEST.STATUSDATE
                    END AS STATUSDATE
          FROM (
               -- Getting latest Status and it's latest Date!
               SELECT    DEL.prop_no,
                         DEL.REPORTCAT,
                         DEL.STATUSDATE,
                         GRP2.REPORTSTAT
               FROM
                    -- Get the Latest Status Date
                    (SELECT   GRP1.prop_no,
                              GRP1.REPORTCAT,
                              MAX(GRP1.STATUSDATE) AS STATUSDATE
                    FROM 
                         (SELECT   prop_no,
                                   REPORTCAT,
                                   REPORTSTAT,
                                   CASE WHEN MAX(CompletedDate) IS NOT NULL AND MAX(CompletedDate) > MAX(ProcessedDate)   
                                        THEN MAX(CompletedDate)
                                        ELSE MAX(ProcessedDate)
                                   END AS STATUSDATE
                         FROM      dbo.vwInfoEdCSCRDeliverables
                         GROUP BY prop_no, REPORTCAT, REPORTSTAT) GRP1 
                    GROUP BY GRP1.prop_no, GRP1.REPORTCAT) DEL
                    INNER JOIN
                    -- Get the Statuses for those Props and Dates
                    (SELECT   prop_no,
                              REPORTCAT,
                              REPORTSTAT,
                              CASE WHEN MAX(CompletedDate) IS NOT NULL AND MAX(CompletedDate) > MAX(ProcessedDate)   
                                   THEN MAX(CompletedDate)
                                   ELSE MAX(ProcessedDate)
                              END AS STATUSDATE
                    FROM      dbo.vwInfoEdCSCRDeliverables
                    GROUP BY prop_no, REPORTCAT, REPORTSTAT) GRP2 ON DEL.prop_no = GRP2.prop_no AND
                                                                      DEL.REPORTCAT = GRP2.REPORTCAT AND
                                                                      DEL.STATUSDATE = GRP2.STATUSDATE
                    ) LATEST 
               -- Outer join with earlier Pending dates. Case statement Above will take of which date we show.
               LEFT OUTER JOIN (                                                       
                         -- Gets the Max of the Min Pending Dates from below
                         SELECT      prop_no,
                                     REPORTCAT,
                                     REPORTSTAT,
                                     MAX(STATUSDATE) AS STATUSDATE
                         FROM (
                              -- Gets Earliest Pending Date Greater than Max Non-Pending Date
                              -- Or the earliest if there is no other status
                              SELECT    D.prop_no,
                                        D.REPORTCAT,
                                        D.REPORTSTAT,
                                        MIN(D.ProcessedDate) AS STATUSDATE,
                                        MAXD.STATUSDATE AS MAXOTHERSTATUSDATE
                              FROM      dbo.vwInfoEdCSCRDeliverables D
                                   LEFT OUTER JOIN -- Gets Latest Non-Pending Status and Date
                                                  (   SELECT    prop_no,
                                                                 REPORTCAT,
                                                                 REPORTSTAT,
                                                                 CASE WHEN MAX(CompletedDate) IS NOT NULL AND MAX(CompletedDate) > MAX(ProcessedDate)   
                                                                      THEN MAX(CompletedDate)
                                                                      ELSE MAX(ProcessedDate)
                                                                 END AS STATUSDATE
                                                       FROM      dbo.vwInfoEdCSCRDeliverables
                                                       WHERE     REPORTSTAT != 'Pending' 
                                                       GROUP BY prop_no, REPORTCAT, REPORTSTAT ) MAXD ON D.prop_no = MAXD.prop_no AND D.REPORTCAT = MAXD.REPORTCAT
                                                  AND D.StatusDate > MAXD.STATUSDATE 
                              WHERE     D.REPORTSTAT = 'Pending' 
                              GROUP BY D.prop_no, D.REPORTCAT, D.REPORTSTAT, MAXD.STATUSDATE ) DEL
                              GROUP BY DEL.prop_no, DEL.REPORTCAT, DEL.REPORTSTAT
                         ) PENDING ON LATEST.prop_no = PENDING.prop_no AND LATEST.REPORTCAT = PENDING.REPORTCAT ) L
     ) STATUS  
     GROUP BY STATUS.prop_no
GO
IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverableTypeStatus') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwInfoEdCSCRDeliverableTypeStatus >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwInfoEdCSCRDeliverableTypeStatus >>>'
GO